import conn
import SQLGenerator
import odbc
from SyntaxParser import buildFindingsHash, combine

path = 'doc/snomed/findings.txt'
findings = buildFindingsHash(path)

time = "2007-08-12 10:00:00"

query2 = "SELECT ptevent.value1 FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid \
AND pteventclass.longlabel = 'PEEP' AND ptevent.entrytime > '2007-08-02 10:00:00' AND \
ptevent.gprid IN (SELECT distinct ptevent.gprid FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.cid = pteventclass.oid AND ptevent.value1_cid = dbcodeconfig_set.oid AND ptevent.value1 = dbcodeconfig_set.elemid \
AND pteventclass.shortlabel = 'VentMode' AND dbcodeconfig_set.shortlabel = 'PS' AND ptevent.entrytime > '"
query2 = query2 + time + "')"
'''
query1 = "SELECT cfgpatients.medrecnum FROM cfgpatients WHERE cfgpatients.gprid IN (" + query2 + ")"

query3 = "SELECT ptevent.value1 FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid \
AND pteventclass.longlabel = 'Heart Rate' AND ptevent.entrytime > '2007-07-30 10:00:00' AND \
ptevent.gprid IN (SELECT ptevent.gprid FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid AND \
pteventclass.longlabel = 'Heart Rate' AND ptevent.value1 > 30)"

query3 = "SELECT convert('real', ptevent.value1) FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid AND \
ptevent.entrytime > '2007-08-02 10:00:00' and pteventclass.longlabel = 'CVP' AND (ptevent.value1 > "
query3 = query3 + '%e' % (20) + ' or ptevent.value1 < %e' % (12) + ')'
'''

query4 = "SELECT cfgpatients.medrecnum FROM cfgpatients WHERE cfgpatients.gprid = 10006173"

#query4 = "SELECT ptevent.value1 FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid AND pteventclass.longlabel = 'PEEP' AND ptevent.entrytime > '2007-08-02 00:00:00' AND ptevent.gprid IN (SELECT DISTINCT ptevent.gprid FROM ptevent, pteventclass, dbcodeconfig_set WHERE ptevent.cid = pteventclass.oid AND ptevent.value1_cid = dbcodeconfig_set.oid AND ptevent.value1 = dbcodeconfig_set.elemid AND pteventclass.longlabel = 'Ventilation Mode' AND dbcodeconfig_set.shortlabel = 'PS' AND ptevent.entrytime > '2007-08-02 00:00:00')"

query4 = "(SELECT distinct ptevent.gprid FROM ptevent, pteventclass \
WHERE ptevent.cid = pteventclass.oid AND ptevent.entrytime > '2007-08-07 10:00:00' AND pteventclass.longlabel = 'CVP' \
AND ptevent.value1 > '1e+1' ) INTERSECT "

query5 = "(SELECT distinct ptevent.gprid FROM ptevent, pteventclass \
WHERE ptevent.cid = pteventclass.oid AND ptevent.entrytime > '2007-08-07 10:00:00' AND pteventclass.longlabel = 'PEEP' \
AND ptevent.value1 > '4e+0')"

query4 = "SELECT DISTINCT pteventclass.longlabel FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.cid = pteventclass.oid AND ptevent.value1_cid = dbcodeconfig_set.oid AND ptevent.value1 = dbcodeconfig_set.elemid "

#query5 = "SELECT TO_NUMBER(ptevent.value1) FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid AND pteventclass.longlabel = 'Heart Rate' AND ptevent.entrytime >= '2007-08-09 08:00:00' AND ptevent.entrytime <= '2007-08-09 10:00:00' AND ptevent.gprid IN ( SELECT ptevent.gprid FROM ptevent, pteventclass WHERE ptevent.cid = pteventclass.oid AND pteventclass.longlabel = 'CVP' AND TO_NUMBER(ptevent.value1) > 20 )"
 
query6 = "SELECT distinct dbcodeconfig_set.longlabel FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.cid = pteventclass.oid AND ptevent.value1_cid = dbcodeconfig_set.oid AND ptevent.value1 = dbcodeconfig_set.elemid \
AND ptevent.entrytime > '" + time + "'"

query7 = "SELECT pteventclass.longlabel FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.value1_cid=dbcodeconfig_set.oid AND ptevent.value1=dbcodeconfig_set.elemid AND ptevent.cid=pteventclass.oid AND \
lower(dbcodeconfig_set.longlabel)='ventilation mode' AND ptevent.entrytime > '" + time + "'"

query8 = "SELECT chartEvents.pid, chartEvents.value1, chartEvents.charttime FROM chartEvents, D_ChartItems \
WHERE chartEvents.itemid = D_ChartItems.itemid AND D_ChartItems.label = 'Heart Rate' \
AND chartEvents.pid IN (SELECT chartEvents.pid FROM chartEvents, D_ChartItems \
WHERE chartEvents.itemid = D_ChartItems.itemid AND D_ChartItems.label = 'PEEP' \
AND chartEvents.value1 >= 4 AND chartEvents.charttime >= to_date('2007-07-12-10', 'YYYY-MM-DD-HH24') \
AND chartEvents.pid IN (SELECT chartEvents.pid FROM chartEvents, D_ChartItems \
WHERE chartEvents.itemid = D_ChartItems.itemid AND D_ChartItems.label = 'Ventilation Mode' \
AND chartEvents.value1 = 'PS' AND chartEvents.charttime >= to_date('2007-07-12-10', 'YYYY-MM-DD-HH24')) ) \
AND chartEvents.charttime >= to_date('2007-07-12-10', 'YYYY-MM-DD-HH24')"

query9 = "SELECT chartEvents.value1 FROM chartEvents, D_ChartItems WHERE chartEvents.itemi\
d = D_ChartItems.itemid AND lower(D_ChartItems.label) = 'heart rate' AND chartEv\
ents.charttime >= to_date('2007-08-12-16','YYYY-MM-DD-HH24') AND chartevents.pid\
 IN (SELECT pid FROM D_Patients WHERE sex='M') AND chartEvents.pid IN (SELECT ch\
artEvents.pid FROM chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartI\
tems.itemid AND lower(D_ChartItems.label) = 'heart rate' AND chartevents.value1\
>'100' AND chartEvents.charttime >= to_date('2007-08-12-16','YYYY-MM-DD-HH24') AN\
D chartEvents.pid IN (SELECT chartEvents.pid FROM chartEvents, D_ChartItems WHER\
E chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = 'peep\
' AND chartevents.value1 >'4' AND chartEvents.charttime >= to_date('2007-08-12-16\
','YYYY-MM-DD-HH24')))"

query4 = "SELECT gprid,pid FROM cfgpatients WHERE medrecnum = 1612406"
query4 = "SELECT mrn, sex FROM D_Patients WHERE pid > 13200"
query4 = "select dayid from d_days where month = 8 and dayofmonth = 19 and year = 2007"

# contains chart-events
query4 = "SELECT D_ChartItems.label, ChartEvents.charttime, ChartEvents.value1 FROM D_ChartItems, ChartEvents, D_Patients \
WHERE ChartEvents.itemid = D_ChartItems.itemid AND ChartEvents.pid = D_Patients.pid AND \
D_Patients.mrn = '1622696' AND ChartEvents.chartdate = 13745"

# ioevents contains dextrose and Saline, etc.

# contains sedation (fentanyl, propofol) and inotropes (noradrenal)
query4 = "SELECT D_MedItems.label, MedEvents.charttime, MedEvents.dose FROM D_MedItems, MedEvents, D_Patients \
WHERE MedEvents.itemid = D_MedItems.itemid AND MedEvents.pid = D_Patients.pid AND \
D_Patients.mrn = '236143' AND MedEvents.chartdate = 13744"

query4 = "SELECT iovolumeevent.volume FROM iovolumeevent, ivdripinfitem WHERE \
iovolumeevent.item_cid = ivdripinfitem.cid AND iovolumeevent.item_oid = ivdripinfitem.oid \
AND iovolumeevent.gprid = ivdripinfitem.gprid \
AND lower(ivdripinfitem.alternatelabel) = 'fentanyl' AND iovolumeevent.entrytime > '2007-08-28 23:00:00'"

query4 = "SELECT pid FROM D_Patients WHERE mrn = '1142612'"
query4 = "SELECT distinct bedconfig.name_ FROM censusevent, bedconfig WHERE censusevent.inbed_oid = bedconfig.oid \
AND censusevent.gprid = 10006239"
query4 = "SELECT chartevents.value1, chartevents.charttime FROM chartevents,D_ChartItems WHERE chartevents.pid = '13438' AND \
chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = 'heart rate' AND \
chartEvents.charttime >= to_date('2007-08-30-00','YYYY-MM-DD-HH24')"
query4 = "Select D_Patients.fullname, censusevents.careUnit FROM D_Patients, censusevents WHERE D_Patients.pid = censusevents.pid \
AND D_Patients.mrn = '1142612'"

query4 = "SELECT count(chartEvents.value1) FROM chartEvents, D_ChartItems WHERE chartEvents.itemi\
d = D_ChartItems.itemid AND lower(D_ChartItems.label) = 'heart rate' AND chartEv\
ents.charttime >= to_date('2007-09-01-02','YYYY-MM-DD-HH24') AND chartEvents.cha\
rttime < to_date('2007-09-01-14','YYYY-MM-DD-HH24') AND chartevents.cuid = 4 AND chartevents.pid <= 10000"

query4 = "Select to_char(D_Patients.dob,'YYYY-MM-DD') FROM D_Patients, censusevents WHERE \
D_Patients.pid = censusevents.pid AND D_Patients.pid > 0 AND to_char(D_Patients.dob,'YYYY-MM-DD') > '1992-01-01 00:00:00.0'"

query4 = "SELECT D_ChartItems.label FROM D_ChartItems order by D_ChartItems.label"
query4 = "SELECT chartEvents.pid FROM chartEvents, D_C\
hartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.\
label) = 'heart rate' AND chartevents.value1 >'60' AND chartEvents.charttime >=\
to_date('2007-09-04-02','YYYY-MM-DD-HH24') AND chartEvents.charttime < to_date('\
2007-09-04-14','YYYY-MM-DD-HH24') AND chartevents.cuid = 4"

query4 = "SELECT D_ChartItems.label, ChartEvents.charttime, ChartEvents.value1 FROM D_ChartItems, ChartEvents, D_Patients \
WHERE ChartEvents.itemid = D_ChartItems.itemid AND ChartEvents.pid = D_Patients.pid AND \
D_Patients.mrn = '1142612' AND D_ChartItems.label = 'fentanyl'"

#query4 = "SELECT D_MedItems.label, MedEvents.charttime, MedEvents.dose FROM D_MedItems, MedEvents, D_Patients \
#WHERE MedEvents.itemid = D_MedItems.itemid AND MedEvents.pid = D_Patients.pid AND \
#D_Patients.mrn = '1142612' AND D_MedItems.label = 'Propofol'"

query4 = "Select D_Patients.pid FROM D_Patients WHERE D_Patients.mrn = '1142612'"
query4 = "SELECT D_ChartItems.label, ChartEvents.charttime, ChartEvents.value1 FROM D_ChartItems, ChartEvents, D_Patients \
WHERE ChartEvents.itemid = D_ChartItems.itemid AND ChartEvents.pid = D_Patients.pid AND \
D_Patients.mrn = '1142612' AND lower(D_ChartItems.label) = 'ventilation mode' AND \
chartEvents.charttime >= to_date('2007-09-08-02','YYYY-MM-DD-HH24')"

query4 = "SELECT distinct medevents.pid FROM medEvents, D_MedItems WHERE \
medEvents.itemid = D_MedItems.itemid AND lower(D_MedItems.label)\
 = 'propofol' AND medevents.dose >'0' AND medEvents.charttime >= to_date('2007-0\
9-08-02','YYYY-MM-DD-HH24') AND medEvents.charttime < to_date('2007-09-08-14','Y\
YYY-MM-DD-HH24') AND medevents.cuid = 4"
# return 13475, 1635, 13529

query4 = "SELECT distinct chartEvents.pid FROM chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartIte\
ms.itemid AND lower(D_ChartItems.label) = 'ventilation mode' AND chartevents.val\
ue1 ='PS' AND chartEvents.charttime >= to_date('2007-09-08-02','YYYY-MM-DD-HH24'\
) AND chartEvents.charttime < to_date('2007-09-08-14','YYYY-MM-DD-HH24') AND chartevents.cuid = 4 \
AND chartEvents.pid >= 10000"
# return 1635, 13376, 13427, 13438, 13529

query4 = "SELECT D_MedItems.label, MedEvents.charttime, MedEvents.dose FROM D_MedItems, MedEvents, D_Patients \
WHERE MedEvents.itemid = D_MedItems.itemid AND MedEvents.pid = D_Patients.pid AND \
D_Patients.mrn = '1142612' AND D_MedItems.label = 'Propofol'"

query4 = "select chartevents.cuid from chartEvents, D_ChartItems WHERE \
chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label)\
 = 'heart rate' AND chartevents.value1 >'0' AND chartEvents.charttime >= to_date('2007-0\
9-08-02','YYYY-MM-DD-HH24') and chartevents.pid = 13427"

query4 = "select D_Patients.mrn, D_MedItems.label, MedEvents.dose, MedEvents.charttime \
FROM D_MedItems, MedEvents, D_Patients \
WHERE MedEvents.itemid = D_MedItems.itemid AND MedEvents.pid = D_Patients.pid AND \
medEvents.charttime >= to_date('2007-09-08-02','YYYY-MM-DD-HH24') AND \
medEvents.charttime < to_date('2007-09-08-14','YYYY-MM-DD-HH24') AND \
lower(D_MedItems.label) IN ('propofol', 'fentanyl', 'morphine')"

query4 = "SELECT D_Patients.mrn, D_MedItems.label, MedEvents.dose, MedEvents.charttime \
FROM D_MedItems, MedEvents, D_Patients WHERE MedEvents.itemid = D_MedItems.itemid \
AND MedEvents.pid = D_Patients.pid AND MedEvents.charttime >= to_date('2007-09-10\
-00','YYYY-MM-DD-HH24') AND MedEvents.charttime < to_date('2007-09-11-00','YYYY-\
MM-DD-HH24') AND lower(D_MedItems.label) IN ('fentanyl','ketamine','midazolam','\
morphine','propofol','remifentanil','thiopentone') AND medevents.cuid = 4 \
AND medevents.pid >= 10000 AND medEvents.pid IN (SELECT pid FROM D_Patients WHERE mrn='1142612')"

query4 = "SELECT DISTINCT gprid FROM cfgpatients WHERE sex='1' AND age_value1>20"

query4 = "SELECT D_ChartItems.label, ChartEvents.charttime, ChartEvents.value1 FROM D_ChartItems, ChartEvents, D_Patients \
WHERE ChartEvents.itemid = D_ChartItems.itemid AND ChartEvents.pid = D_Patients.pid AND \
D_Patients.mrn = '1390622' AND lower(D_ChartItems.label) = 'heart rate' AND \
chartEvents.charttime >= to_date('2007-08-12-00','YYYY-MM-DD-HH24')"

query4 = "SELECT dbcodeconfig_set.longlabel FROM progressnote, dbcodeconfig_set WHERE \
progressnote.notetype_cid = dbcodeconfig_set.oid \
AND progressnote.notetype = dbcodeconfig_set.elemid \
AND progressnote.gprid = 10006285"

query4 = "SELECT noteevents.notetext from noteevents WHERE pid = 6372"

# display all lab-event attributes for realtime db
query4 = "SELECT distinct labeventclass.longlabel FROM labevent, labeventclass WHERE labevent.cid = labeven\
tclass.oid AND labevent.gprid IN (SELECT DISTINCT gprid FROM cfgpatients WHERE medrecnum IN \
('1627607','1627423','1390622','1629464','1628269','1628314','1628245','1627979','1608333','1629977','1423875')) \
ORDER BY labeventclass.longlabel"

# display all med-event (inotropes and sedation, etc) attributes for realtime db
query4 = "SELECT distinct ivdripinfitem.alternatelabel FROM ivdripinfitem, iovolumeevent WHERE \
ivdripinfitem.gprid = iovolumeevent.gprid AND ivdripinfitem.cid = iovolumeevent.item_cid \
AND ivdripinfitem.oid = iovolumeevent.item_oid AND \
iovolumeevent.gprid IN (SELECT DISTINCT gprid FROM cfgpatients WHERE medrecnum IN \
('1627607','1627423','1390622','1629464','1628269','1628314','1628245','1627979','1608333','1629977','1423875')) \
ORDER BY ivdripinfitem.alternatelabel"

# display all chart-event (numerical and categoric) attributes for realtime db
query4 = "SELECT gprid FROM cfgpatients WHERE medrecnum = '1627979'"
query4 = "SELECT pteventclass.longlabel FROM ptevent, pteventclass WHERE ptevent.cid = ptevent\
class.oid AND ptevent.gprid = 10006285 AND ptevent.entrytime > '2007-09-26 00:00:00'"

query4 = "SELECT pid, notetext FROM noteevents where pid <= 1000 ORDER BY pid"

query4 = "SELECT chartevents.value1 from chartevents, d_chartitems where chartevents.itemid = d_chartitems.itemid and lower(d_chartitems.label) = 'heart rate'and chartevents.pid = 3000"
query4 = "SELECT DISTINCT ptevent.gprid FROM ptevent, pteventclass, dbcodeconfig_set WHERE \
ptevent.cid = pteventclass.oid AND ptevent.value1_cid = dbcodeconfig_set.oid AND \
ptevent.value1 = dbcodeconfig_set.elemid AND lower(pteventclass.longlabel) = 'ventilation mode' \
AND lower(dbcodeconfig_set.shortlabel) = 'PS' AND ptevent.entrytime > '2007-10-21 17:00:00'"

query4 = "SELECT count(intime) from censusevents group by pid"

query4 = "SELECT chartEvents.value1 FROM chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = 'heart rate' AND chartEvents.charttime >= to_date('2007-10-12-16','YYYY-MM-DD-HH24') AND chartEvents.charttime < to_date('2007-10-24-16','YYYY-MM-DD-HH24') AND chartevents.cuid = 4 AND chartEvents.pid IN ('13028', '13029', '13032', '13038', '13061', '13090', '13105', '13156', '13163', '13164', '13167', '13188', '13197', '13202', '13205', '13211', '13213', '13226', '13227', '13232', '13234', '13248', '13253', '13269', '13272', '13291', '13317', '13325', '13328', '13343', '13363', '13366', '13392', '13405', '13413', '13415', '13438', '13439', '13440', '13441', '13462', '13475', '13507', '13520', '13529', '13539', '13547', '13549', '13555', '13565', '13669')"
query4 = "SELECT intime from censusevents where pid = 12000"

query4 = "SELECT chartEvents.value1 FROM chartEvents, D_ChartItems WHERE chartEvents.itemid = D_ChartItems.itemid AND lower(D_ChartItems.label) = 'heart rate' AND chartEvents.charttime >= to_date('2007-10-20-15','YYYY-MM-DD-HH24') AND chartEvents.charttime < to_date('2007-10-28-15','YYYY-MM-DD-HH24') AND chartevents.cuid = 4 AND chartevents.pid > 10000 AND chartEvents.pid IN (SELECT pid FROM D_Patients WHERE mrn='1596826')"
query4 = "select gprid from cfgpatients where cfgpatients.age_value1 > 16"

ism_db = "ism/ism/ism4u"
gicu_db = "gicu/hps/notroot"
db = None
c = None
r = []
db = odbc.odbc(gicu_db)
c = db.cursor()
c.execute(query4)

results = c.fetchall()
"""
for key in findings.keys():
    query4 = "SELECT pid from noteevents where noteText like \"%" + key + "%\""
    print query4  
    c.execute(query4)
    result = c.fetchall()
    results = combine(results, result, 'or')
"""
c.close()

for n in range(0, len(results)):
    print results[n][0]
    #print str(result[n][0]) + ' ' + str(result[n][1])
    #print str(result[n][0]) + ' ' + str(result[n][1]) + ' ' + str(result[n][2])
    #print str(result[n][0]) + ' ' + str(result[n][1]) + ' ' + str(result[n][2]) + ' ' + str(result[n][3])
"""
c.execute(query4)
result = c.fetchall()
c.close()
for n in range(0, len(result)):
    print "<note>"
    print "<pid>" + str(result[n][0]) + "</pid>"
    print "<notetext>" + str(result[n][1]) + "</notetext>"
    print "</note>"
"""
